package com.ssh.url.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.ssh.db.util.DBUtil;
import com.ssh.url.model.Url;

public class UrlDao {
	public static List<Url> getList() {
		List<Url> list = new ArrayList<Url>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		String sql = DBUtil.SELECT_SQL + "t_url";
		try {
			conn = DBUtil.getConn();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Url url = new Url();
				url.setId(rs.getInt("id"));
				url.setKeyword(rs.getString("keyword"));
				url.setUrl(rs.getString("url"));
				list.add(url);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(pstmt, rs, conn);
		}
		return list;
	}
	public static List<Url> getList(int count) {
		List<Url> list = new ArrayList<Url>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		String sql = DBUtil.SELECT_SQL + "t_url limit ?";
		try {
			conn = DBUtil.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, count);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Url url = new Url();
				url.setId(rs.getInt("id"));
				url.setKeyword(rs.getString("keyword"));
				url.setUrl(rs.getString("url"));
				list.add(url);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(pstmt, rs, conn);
		}
		return list;
	}
	public static List<Url> searchUrlByKeyword(String keyword) {
		List<Url> list = new ArrayList<Url>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		String sql = DBUtil.SELECT_SQL + "t_url where keyword=?";

		try {
			conn = DBUtil.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, keyword);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Url url = new Url();
				url.setId(rs.getInt("id"));
				url.setKeyword(rs.getString("keyword"));
				url.setUrl(rs.getString("url"));
				list.add(url);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(pstmt, rs, conn);
		}
		return list;
	}
	public static List<Url> searchUrlByKeywordWithLimit(String keyword,int count) {
		List<Url> list = new ArrayList<Url>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		String sql = DBUtil.SELECT_SQL + "t_url where keyword=? limit ?";

		try {
			conn = DBUtil.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, keyword);
			pstmt.setInt(2, count);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Url url = new Url();
				url.setId(rs.getInt("id"));
				url.setKeyword(rs.getString("keyword"));
				url.setUrl(rs.getString("url"));
				list.add(url);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(pstmt, rs, conn);
		}
		return list;
	}
	public static int getTotalCount(String tname) {
		return DBUtil.getTotalCount(tname);
	}
	public static void main(String[] args) {
//		List<Url> list = searchUrlByKeywordWithLimit("keyword_1",50);
//		List<Url> list = getList(500);
//		for (Url u : list) {
//			System.out.println(u.getId() + " : " + u.getKeyword() + " --|--" + u.getUrl());
//		}
		System.out.println(getTotalCount("t_url"));
	}
}
